"""sql 查询文件"""
import json
import logging

from global_file import SELJIANLI

from database.connection import Link_database
# from ydsp.database.connection import Link_database


logger = logging.getLogger(__name__)


def alls():
    """获取所有id"""
    con, obj = Link_database()

    sql = """select id from user"""
    obj.execute(sql)
    query = obj.fetchall()
    user = []
    for i in query:
        user.append(i[0])

    return user


def all_user_id_list(user_id):
    """获得权限下所有id"""
    con, obj = Link_database()

    sql = """select user_id from depar_user where director=%d""" % user_id
    obj.execute(sql)
    user_id_tupe = obj.fetchall()
    if not user_id_tupe:
        sql = """select user_id from depar_user where tube=%d""" % user_id
        obj.execute(sql)
        user_id_tupe = obj.fetchall()

    user = [0]
    if user_id_tupe:
        for i in user_id_tupe:
            user.append(i[0])

    return user


def select_user(user_id_in):
    """查询权限下所有用户"""
    con, obj = Link_database()

    sql = """select c.*,d.checked from permission_table as d inner join (select a.id,a.name,a.phone,b.department,a.jobs,a.state from user as a left join depar as b on a.department=b.id where a.id in {}) as c on c.id=d.user_id""".format(user_id_in)
    obj.execute(sql)
    q = obj.fetchall()

    return q


class ResumeSel(object):
    """简历查询"""
    def __init__(self, user_id):
        self.user_id = user_id
        self.con, self.obj = Link_database()

    def alls(self):
        sql = """select id from user"""
        self.obj.execute(sql)
        query = self.obj.fetchall()
        user = []
        for i in query:
            user.append(i[0])

        return user

    def select_resum(self):
        """查询简历"""
        sql = """select b.name,b.phone,b.born_date,b.school,b.professional,b.xz_address,b.jt_address,b.members,
                      a.apply_position,a.expected_salary,a.work_experience,a.education_experience,a.opinion from resume as a inner join user as b on a.user_id=b.id where a.user_id={} order by a.id desc""".format(
            self.user_id)
        self.obj.execute(sql)
        query = self.obj.fetchone()

        return query


class WorkPlanSel(object):
    """查看工作计划"""
    def __init__(self, user_id, page, start_time, end_time):
        self.user_id = user_id
        self.page = page
        # self.is_type = is_type
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def month_log(self):
        """计划"""
        # sql = """select a.id,a.statdate,a.is_type,a.department,a.title,a.content,b.name from month_log as a inner join user as b on a.user_id=b.id where a.is_type='{}' and a.user_id in {} and a.statdate between {} and {} order by id desc limit {},20""".format(self.is_type, self.u_tuple, self.start_time, self.end_time, self.page)
        sql = """select c.*,d.department from depar as d inner join (select a.id,a.statdate,a.is_type,a.department,a.title,b.name from month_log as a inner join user as b on a.user_id=b.id where a.user_id in {} and a.statdate between {} and {}) as c on c.department=d.id""".format(
            self.u_tuple, self.start_time, self.end_time)

        return sql

    # def weeks_log(self):
    #     """个人周计划"""
    #     sql = """select id,statdate,name,title,file_address,shop_name from weeks_log where user_id in {} and statdate between {} and {} order by id desc limit {},20""".format(self.u_tuple, self.start_time, self.end_time, self.page)
    #
    #     return sql
    #
    # def job_log(self):
    #     """个人日计划"""
    #     sql = """select id,statdate,name,title,file_address,shop_name from job_log where user_id in {} and statdate between {} and {} order by id desc limit {},20""".format(self.u_tuple, self.start_time, self.end_time, self.page)
    #
    #     return sql

    def deal_with(self,):
        """数据处理"""
        if self.user_id in SELJIANLI:
            self.u_tuple = tuple(alls())
        else:
            u_list = all_user_id_list(self.user_id)
            u_list.append(self.user_id)
            self.u_tuple = tuple(u_list)

    def main(self):
        self.deal_with()
        sql = self.month_log()
        self.obj.execute(sql)
        query = self.obj.fetchall()

        return query

    def __del__(self):
        self.obj.close()
        self.con.close()


class AllWorkPlanSel(object):
    """查看所有工作计划"""
    def __init__(self, user_id):
        self.user_id = user_id
        self.con, self.obj = Link_database()

    def alls(self):
        sql = """select id from user"""
        self.obj.execute(sql)
        query = self.obj.fetchall()
        user = []
        for i in query:
            user.append(i[0])

        return user

    # def user_id_list(self):
    #     """获得所有id"""
    #     sql = """select user_id from depar_user where director=%d""" % self.user_id
    #     self.obj.execute(sql)
    #     user_id_tupe = self.obj.fetchall()
    #     if not user_id_tupe:
    #         sql = """select user_id from depar_user where tube=%d""" % self.user_id
    #         self.obj.execute(sql)
    #         user_id_tupe = self.obj.fetchall()
    #
    #     user = []
    #     if user_id_tupe:
    #         for i in user_id_tupe:
    #             user.append(i[0])
    #
    #     return user

    def month_log(self, user_id_in):
        """个人月计划"""
        sql = """select statdate,name,title,content from month_log where user_id in {} order by statdate desc limit 100""".format(user_id_in)

        return sql

    def weeks_log(self, user_id_in):
        """个人周计划"""
        sql = """select statdate,name,title,content from month_log where user_id in {} order by statdate desc limit 100""".format(user_id_in)

        return sql

    def job_log(self, user_id_in):
        """个人日计划"""
        sql = """select statdate,name,title,content from month_log where user_id in {} order by statdate desc limit 100""".format(user_id_in)

        return sql

    def main(self, sql):
        self.obj.execute(sql)
        query = self.obj.fetchall()

        return query

    def __del__(self):
        self.obj.close()
        self.con.close()


class sel_work_plan(object):
    """工作计划条件查询"""
    def __init__(self, page, user_id, department, start_time, end_time):
        self.page = page
        self.user_id = user_id
        self.department = department
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def deal_with(self,):
        """数据处理"""
        if self.user_id in SELJIANLI:
            self.u_tuple = tuple(alls())
        else:
            u_list = all_user_id_list(self.user_id)
            u_list.append(self.user_id)
            self.u_tuple = tuple(u_list)

    def perform(self, is_type):
        self.deal_with()
        if self.department:
            sql = """select c.*,d.department from depar as d inner join (select a.id,a.statdate,a.is_type,a.department,a.title,b.name from month_log as a inner join user as b on a.user_id=b.id where a.department={} and a.is_type='{}' and a.user_id in {} and a.statdate between {} and {} order by a.id limit {},20) as c on c.department=d.id""".format(self.department, is_type, self.u_tuple, self.start_time, self.end_time, self.page)
        else:
            sql = """select c.*,d.department from depar as d inner join (select a.id,a.statdate,a.is_type,a.department,a.title,b.name from month_log as a inner join user as b on a.user_id=b.id where a.is_type='{}' and a.user_id in {} and a.statdate between {} and {} order by a.id limit {},20) as c on c.department=d.id""".format(is_type, self.u_tuple, self.start_time, self.end_time, self.page)
        self.obj.execute(sql)
        query = self.obj.fetchall()

        return query

    def __del__(self):
        self.obj.close()
        self.con.close()


def work_details(id, user_id):
    """工作计划详情查询"""
    con, obj = Link_database()

    sql = """select c.*,d.department from depar as d inner join (select a.id,a.statdate,a.is_type,a.department,a.title,a.content,a.notation,b.name from month_log as a inner join user as b on a.user_id=b.id where a.id=%d) as c on c.department=d.id""" % id

    obj.execute(sql)
    q = obj.fetchone()

    sql_1 = """update message_table set is_read='True' where file_id={} and receive_id={}""".format(id, user_id)
    obj.execute(sql_1)
    con.commit()

    obj.close()
    con.close()

    return q


class open_permissions(object):
    """权限查看"""
    def __init__(self, user_id):
        self.user_id = user_id
        self.con, self.obj = Link_database()

    def sel_data(self):
        """查询"""
        sql = """select id,user_id,checked,permission_data from permission_table where user_id={}""".format(self.user_id)

        self.obj.execute(sql)
        q = self.obj.fetchone()

        return q

    def deal_with(self):
        """处理"""
        q = self.sel_data()
        data = {
            "id": q[0],
            "user_id": q[1],
            "checked": json.loads(q[2]),
            "sideNavData": json.loads(q[3]),
        }

        return data

    def __del__(self):
        self.obj.close()
        self.con.close()

